﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Entidades.Reclamos;
using System.Data;
using Datos.Reclamos.Common;
using Datos.Reclamos.DAL;

namespace Datos.Reclamos
{
    public class RecLibroReclamacionDAL : ConexionBD, IRecLibroReclamacionDAL
    {
        #region ATRIBUTOS       
        
        private SqlCommand cmd;

        #endregion

        #region METODOS PARA LOS LIBROS DE RECLAMACIONES

        public List<RecLibro> listarLibros()
        {
            List<RecLibro> lista = new List<RecLibro>();
            RecLibro be;

            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_ListarLibro";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();
            
            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        be = new RecLibro();
                        be.IdLibro = dr.GetInt32(dr.GetOrdinal("idLibro"));
                        be.Descripcion = dr.GetString(dr.GetOrdinal("descripcion"));  
                        lista.Add(be);
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
            return lista;
        }

        #endregion

        #region METODOS PARA LOS PRODUCTOS / SERVICIOS OFRECIDOS

        public List<GenLocal> listarLocal()
        {
            List<GenLocal> lista = new List<GenLocal>();
            GenLocal be;

            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_ListarLocal";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        be = new GenLocal();
                        be.IdLocal = dr.GetInt32(dr.GetOrdinal("idLocal"));
                        be.Nombre = dr.GetString(dr.GetOrdinal("nombre"));
                        lista.Add(be);
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
            return lista;
        }

        #endregion


        #region METODOS PARA LOS LIBROS DE RECLAMACIONES

        public int insertar(RecLibro libro)
        {
            
            int codInsertado = 0;

            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_InsertarLibroReclamacion";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            cmd.Parameters.Add("@pIdLocal", SqlDbType.Int, 11).Value = libro.Local.IdLocal;
            cmd.Parameters.Add("@pDescripcion", SqlDbType.VarChar, 1000).Value = libro.Descripcion;            
            cmd.Parameters.Add("@pNumRegistro", SqlDbType.VarChar,20).Value = libro.NumRegistro;
            cmd.Parameters.Add("@pMedida", SqlDbType.VarChar, 20).Value = libro.Medida;
            cmd.Parameters.Add("@pNumHojas", SqlDbType.VarChar, 11).Value = libro.numHojas;
            cmd.Parameters.Add("@pFlVirtual", SqlDbType.Bit).Value = libro.flLibro;            
            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        codInsertado = Convert.ToInt32(dr["resultado"]);
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
               cmd.Connection.Close();
               cmd.Connection.Dispose();
               cmd.Dispose();              
            }
            return codInsertado;
        }

        public int actualizar(RecLibro libro)
        {
            int codInsertado = 0;
            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_ActualizarLibroReclamacion";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            cmd.Parameters.Add("@pIdLibro", SqlDbType.Int, 11).Value = libro.IdLibro;
            cmd.Parameters.Add("@pIdLocal", SqlDbType.Int, 11).Value = libro.Local.IdLocal;
            cmd.Parameters.Add("@pDescripcion", SqlDbType.VarChar, 1000).Value = libro.Descripcion;
            cmd.Parameters.Add("@pNumRegistro", SqlDbType.VarChar, 20).Value = libro.NumRegistro;
            cmd.Parameters.Add("@pMedida", SqlDbType.VarChar, 20).Value = libro.Medida;
            cmd.Parameters.Add("@pNumHojas", SqlDbType.VarChar, 11).Value = libro.numHojas;
            cmd.Parameters.Add("@pFlVirtual", SqlDbType.Bit).Value = libro.flLibro;     

            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        codInsertado = Convert.ToInt32(dr["resultado"]);
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose(); 
            }
            return codInsertado;
        }

        public RecLibro obtener(int idLibro)
        {
            RecLibro be = new RecLibro();
            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_ObtenerLibro";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            cmd.Parameters.Add("@pIdLibro", SqlDbType.Int, 11).Value = idLibro;

            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        be = new RecLibro();
                        be.IdLibro = idLibro;
                        be.Local.IdLocal = dr.GetInt32(dr.GetOrdinal("idLocal"));
                        be.Descripcion = dr.GetString(dr.GetOrdinal("descripcion"));
                        be.NumRegistro = dr.GetString(dr.GetOrdinal("numRegistro"));
                        be.Medida = dr.GetString(dr.GetOrdinal("medida"));
                        be.numHojas = dr.GetInt32(dr.GetOrdinal("numHojas"));
                        be.flLibro = dr.GetBoolean(dr.GetOrdinal("flVirtual"));
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose(); 
            }
            return be;
        }

        public List<RecLibro> listar(RecLibro libro)
        {
            List<RecLibro> lista = new List<RecLibro>();
            RecLibro be;
            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_ListarLibroReclamacion";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            cmd.Parameters.Add("@pCodigo", SqlDbType.Int,11).Value = libro.IdLibro;
            cmd.Parameters.Add("@pDescripcion", SqlDbType.VarChar,1000).Value = libro.Descripcion;
            cmd.Parameters.Add("@pLocal", SqlDbType.Int,11).Value = libro.Local.IdLocal;
            
            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        be = new RecLibro();
                        be.IdLibro = dr.GetInt32(dr.GetOrdinal("idLibro"));
                        be.Local.IdLocal = dr.GetInt32(dr.GetOrdinal("idLocal"));
                        be.Local.Nombre = dr.GetString(dr.GetOrdinal("local"));
                        be.Descripcion = dr.GetString(dr.GetOrdinal("descripcion"));
                        be.NumRegistro = dr.GetString(dr.GetOrdinal("numRegistro"));
                        be.Medida = dr.GetString(dr.GetOrdinal("medida"));
                        be.numHojas = dr.GetInt32(dr.GetOrdinal("numHojas"));
                        lista.Add(be);
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
            return lista;
        }

        public int eliminar(int codigo)
        {
            int resultado = 0;
            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_EliminarLibro";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            cmd.Parameters.Add("@pIdLibro", SqlDbType.Int, 11).Value = codigo;

            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        resultado = dr.GetInt32(dr.GetOrdinal("resultado"));
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
            return resultado;
        }

        public int obtenerNumHoja()
        {
            int resultado = 0;
            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_ObtenerNumHoja";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();            

            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        resultado = dr.GetInt32(dr.GetOrdinal("resultado"));
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
            return resultado;
        }

        #endregion

    }
}